using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
	/// <summary> 
	///Author: nnamthach@gmail.com 
	/// <summary>
    public class OpRtuDataAccess : Connection
    {
		#region Local Variable
        public OpRtuDataAccess(string type, string connectString, int timeout = 0) : base(type, connectString, timeout) { }
        public OpRtuDataAccess(Connection connection) : base(connection) { }
		#endregion Local Variable
		
		#region Method        
		public DataTable GetShortData(String XuongId, List<DTO.FilterParameter> filters, ref string sErr) {
            string sql = @"SELECT ComId LineId, RtuID,  
            FROM  OpRtu
            WHERE  XuongId = @XuongId";
			
			bool hasfilters = BeginFilterParameter(ref sql, filters);

            InitConnect();
            InitCommand(sql);
            AddParameter(OpRtuInfo.Field.XuongId, XuongId);
            if (hasfilters) AddFilterParameter(filters);
			
            DataTable list = new DataTable();
            try { list = executeSelect(); }
            catch (Exception ex) { sErr = string.Format("{0} sql='{1}'", ex.Message, sql); }

            return list;
        }
		
		public DataTable GetByFilterToDataTable(String XuongId, List<DTO.FilterParameter> filters, ref string sErr, int indexPage = 0, int itemPerPage = 0) {            
            string sql = @"SELECT  XuongId, ComId, LineId, RtuID, LenhSX, NguoiTH, DonVi
            FROM  OpRtu
            WHERE  XuongId = @XuongId";

            bool hasfilters = BeginFilterParameter(ref sql, filters);
			
			sql += @" ORDER BY  XuongId, ComId, LineId, RtuID";
			
            if (itemPerPage != 0) {
                if (DbType == "S") sql += @" OFFSET @indexPage ROWS FETCH NEXT @itemPerPage ROWS ONLY";
                else sql += @" LIMIT @indexPage ,@itemPerPage ";
            }
            
            InitConnect();
            InitCommand(sql);
            AddParameter(OpRtuInfo.Field.XuongId, XuongId);
            if (itemPerPage != 0) {
                AddParameter("indexPage", indexPage);
                AddParameter("itemPerPage", itemPerPage);
            }

			if (hasfilters) AddFilterParameter(filters);
			
            DataTable list = new DataTable();
            try { list = executeSelect(); }
			catch (Exception ex) { sErr = string.Format("{0} sql='{1}'", ex.Message, sql); }

            return list;
        }
		
		
		public OpRtuInfo[] GetByFilter(String XuongId, List<DTO.FilterParameter> filters, ref string sErr, int indexPage = 0, int itemPerPage = 0) {
			DataTable list = GetByFilterToDataTable(XuongId, filters, ref sErr, indexPage, itemPerPage);

			if (sErr != "") return null;
			
            OpRtuInfo[] res = null;            
			
			try
			{
				res = new OpRtuInfo[list.Rows.Count];
				for (int i = 0; i < list.Rows.Count; i++)
					res[i] = new OpRtuInfo(list.Rows[i]);
			}
			catch (Exception ex) { sErr = ex.Message; }
            
			if (sErr != "") return null;
            return res;
        }
		
		public int GetCountRecord(String XuongId, List<DTO.FilterParameter> filters, ref string sErr) {
            string sql = @"SELECT COUNT(*)
                FROM  OpRtu
                WHERE  XuongId = @XuongId";
				
			bool hasfilters = BeginFilterParameter(ref sql, filters);
			
			int ret = -1;
            openConnection();
            InitCommand(sql);
            AddParameter(OpRtuInfo.Field.XuongId, XuongId);
          
			if (hasfilters) AddFilterParameter(filters);
		  
            try
            {
                object tmp = executeScalar();

                if(tmp != null && tmp != DBNull.Value) ret = Convert.ToInt32(tmp);
				else ret = 0;
            }
            catch (Exception ex) { sErr = string.Format("{0} sql='{1}'", ex.Message, sql); }
            finally { closeConnection(); }
			
            return ret;			
        }
		
		/// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public int Add(OpRtuInfo objEntr, ref string sErr) {
            string sqlFields = @" INSERT INTO OpRtu(XuongId,ComId,LineId,RtuID,";
            string sqlValues = @" VALUES(@XuongId,@ComId,@LineId,@RtuID,";

            if (objEntr.LenhSX != null) { sqlFields += " LenhSX,"; sqlValues += " @LenhSX,"; }
            if (objEntr.NguoiTH != null) { sqlFields += " NguoiTH,"; sqlValues += " @NguoiTH,"; }
            if (objEntr.DonVi != null) { sqlFields += " DonVi,"; sqlValues += " @DonVi,"; }
/* Can not combine this 			if (objEntr.<%CreateColumn(Name)%> != null) { sqlFields += " <%CreateColumn(FieldName)%>,"; sqlValues += " @<%CreateColumn(FieldName)%>,"; }*/
/* Can not combine this 			if (objEntr.<%UpdateColumn(Name)%> != null) { sqlFields += " <%UpdateColumn(FieldName)%>,"; sqlValues += " @<%UpdateColumn(FieldName)%>,"; }*/

            sqlFields = sqlFields.Substring(0, sqlFields.Length - 1) + ")";
            sqlValues = sqlValues.Substring(0, sqlValues.Length - 1) + ")";

            int ret = -1;
			string sql = sqlFields + sqlValues;
            openConnection();
            InitCommand(sql);
            AddParameter(OpRtuInfo.Field.XuongId, objEntr.XuongId);
            AddParameter(OpRtuInfo.Field.ComId, objEntr.ComId);
            AddParameter(OpRtuInfo.Field.LineId, objEntr.LineId);
            AddParameter(OpRtuInfo.Field.RtuID, objEntr.RtuID);

            if (objEntr.LenhSX != null) AddParameter(OpRtuInfo.Field.LenhSX, objEntr.LenhSX);         
            if (objEntr.NguoiTH != null) AddParameter(OpRtuInfo.Field.NguoiTH, objEntr.NguoiTH);         
            if (objEntr.DonVi != null) AddParameter(OpRtuInfo.Field.DonVi, objEntr.DonVi);         
/* Can not combine this 			if (objEntr.<%CreateColumn(Name)%> != null) AddParameter(OpRtuInfo.Field.<%CreateColumn(Name)%>, objEntr.<%CreateColumn(Name)%>);*/
/* Can not combine this 			if (objEntr.<%UpdateColumn(Name)%> != null) AddParameter(OpRtuInfo.Field.<%UpdateColumn(Name)%>, objEntr.<%UpdateColumn(Name)%>);*/
          
            try
            {
                object tmp = executeScalar();
                if(tmp != null && tmp != DBNull.Value) ret = Convert.ToInt32(tmp);
				else ret = 0;
            }
            catch (Exception ex) { sErr = string.Format("{0} sql='{1}'", ex.Message, sql); }
            finally { closeConnection(); }

            return ret;
        }

        public string Update(OpRtuInfo objEntr) {
            string sql = @"UPDATE OpRtu SET ";
			
            if (objEntr.LenhSX != null) { sql += "LenhSX = @LenhSX,"; }
            if (objEntr.NguoiTH != null) { sql += "NguoiTH = @NguoiTH,"; }
            if (objEntr.DonVi != null) { sql += "DonVi = @DonVi,"; }
/* Can not combine this 			if (objEntr.<%UpdateColumn(Name)%> != null) { sql += "<%UpdateColumn(FieldName)%> = @<%UpdateColumn(FieldName)%>,"; }*/

			//sql += " lastupdate = getdate()";
            sql = sql.Substring(0,sql.Length - 1);
            sql += @" WHERE  XuongId = @XuongId AND ComId = @ComId AND LineId = @LineId AND RtuID = @RtuID";

            openConnection();
            InitCommand(sql);

            AddParameter(OpRtuInfo.Field.XuongId, objEntr.XuongId);
            AddParameter(OpRtuInfo.Field.ComId, objEntr.ComId);
            AddParameter(OpRtuInfo.Field.LineId, objEntr.LineId);
            AddParameter(OpRtuInfo.Field.RtuID, objEntr.RtuID);

            if (objEntr.LenhSX != null) AddParameter(OpRtuInfo.Field.LenhSX, objEntr.LenhSX);         
            if (objEntr.NguoiTH != null) AddParameter(OpRtuInfo.Field.NguoiTH, objEntr.NguoiTH);         
            if (objEntr.DonVi != null) AddParameter(OpRtuInfo.Field.DonVi, objEntr.DonVi);         
/* Can not combine this 			if (objEntr.<%UpdateColumn(Name)%> != null) AddParameter(OpRtuInfo.Field.<%UpdateColumn(Name)%>, objEntr.<%UpdateColumn(Name)%>);          */
               
            string sErr = "";

            try { executeNonQuery(); }
            catch (Exception ex) { sErr = string.Format("{0} sql='{1}'", ex.Message, sql); }
            finally { closeConnection(); }

            return sErr;
        }

        public string Delete(String XuongId, Int32 ComId, Int32 LineId, Int32 RtuID) {
            string sql = @"DELETE OpRtu
            WHERE 	 XuongId = @XuongId AND ComId = @ComId AND LineId = @LineId AND RtuID = @RtuID";

            openConnection();
            InitCommand(sql);

            AddParameter(OpRtuInfo.Field.XuongId, XuongId);
            AddParameter(OpRtuInfo.Field.ComId, ComId);
            AddParameter(OpRtuInfo.Field.LineId, LineId);
            AddParameter(OpRtuInfo.Field.RtuID, RtuID);
              
            string sErr = "";
            try { executeNonQuery(); }
            catch (Exception ex) { sErr = string.Format("{0} sql='{1}'", ex.Message, sql); }
            finally { closeConnection(); }

            return sErr;
        }
        public string Delete(String XuongId, Int32 ComId, Int32 LineId)
        {
            string sql = @"DELETE OpRtu
            WHERE 	 XuongId = @XuongId AND ComId = @ComId AND LineId = @LineId ";

            openConnection();
            InitCommand(sql);

            AddParameter(OpRtuInfo.Field.XuongId, XuongId);
            AddParameter(OpRtuInfo.Field.ComId, ComId);
            AddParameter(OpRtuInfo.Field.LineId, LineId);

            string sErr = "";
            try { executeNonQuery(); }
            catch (Exception ex) { sErr = string.Format("{0} sql='{1}'", ex.Message, sql); }
            finally { closeConnection(); }

            return sErr;
        }   
        public Boolean IsExist(String XuongId, Int32? ComId, Int32? LineId, Int32? RtuID, ref string sErr) {
            string sql = @"SELECT TOP (1) XuongId, ComId, LineId, RtuID
            FROM  OpRtu
            WHERE 	 XuongId = @XuongId AND ComId = @ComId AND LineId = @LineId AND RtuID = @RtuID";
			
            InitConnect();
            InitCommand(sql);
            AddParameter(OpRtuInfo.Field.XuongId, XuongId);              
            AddParameter(OpRtuInfo.Field.ComId, ComId);              
            AddParameter(OpRtuInfo.Field.LineId, LineId);              
            AddParameter(OpRtuInfo.Field.RtuID, RtuID);              
            
            DataTable list = new DataTable();
            try { list = executeSelect(); }
            catch (Exception ex) { sErr = ex.Message + " sql='" + sql + "'"; }

			return list.Rows.Count == 1;
        }		
		
		#endregion Method
     
    }
}
